﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
namespace SZSLSystem.DAL
{
	/// <summary>
	///数据访问类 SubscriptionAgreement (create by HC 2013/1/14 22:28:42)
	/// </summary>
	public class SubscriptionAgreement
	{
		public SubscriptionAgreement(){}
		public bool Exists(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(OID) from SubscriptionAgreement");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			return DbHelperOleDb.Exists(strSql.ToString(), parameters);
		}

		public int Delete(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("delete from SubscriptionAgreement");
			strSql.Append(" where OID=@OID  ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			return DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
		}

		public int Add(SZSLSystem.Model.SubscriptionAgreement model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("insert into SubscriptionAgreement( ");
			strSql.Append("[OID],[BuyerOID],[RoomOID],[RoomType],[ApprovalAuthority],[LicenseNumber],[Proprietorship],[GroundLicenseNumber],[PriceType],[UnitPrice],[TotalPrice],[BuyType],[Payments],[PayMoney],[Remark],[SignTime],[SignAddress])");
			strSql.Append("values (@OID,@BuyerOID,@RoomOID,@RoomType,@ApprovalAuthority,@LicenseNumber,@Proprietorship,@GroundLicenseNumber,@PriceType,@UnitPrice,@TotalPrice,@BuyType,@Payments,@PayMoney,@Remark,@SignTime,@SignAddress)");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50),
					new SqlParameter ("@BuyerOID",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomOID",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomType",SqlDbType.VarChar,50),
					new SqlParameter ("@ApprovalAuthority",SqlDbType.VarChar,50),
					new SqlParameter ("@LicenseNumber",SqlDbType.VarChar,50),
					new SqlParameter ("@Proprietorship",SqlDbType.VarChar,50),
					new SqlParameter ("@GroundLicenseNumber",SqlDbType.VarChar,50),
					new SqlParameter ("@PriceType",SqlDbType.VarChar,50),
					new SqlParameter ("@UnitPrice",SqlDbType.VarChar,50),
					new SqlParameter ("@TotalPrice",SqlDbType.VarChar,50),
					new SqlParameter ("@BuyType",SqlDbType.VarChar,50),
					new SqlParameter ("@Payments",SqlDbType.VarChar,50),
					new SqlParameter ("@PayMoney",SqlDbType.VarChar,50),
					new SqlParameter ("@Remark",SqlDbType.VarChar,1000),
					new SqlParameter ("@SignTime",SqlDbType.DateTime),
					new SqlParameter ("@SignAddress",SqlDbType.VarChar,50)};
			parameters[0].Value = model.OID;
			parameters[1].Value = model.BuyerOID;
			parameters[2].Value = model.RoomOID;
			parameters[3].Value = model.RoomType;
			parameters[4].Value = model.ApprovalAuthority;
			parameters[5].Value = model.LicenseNumber;
			parameters[6].Value = model.Proprietorship;
			parameters[7].Value = model.GroundLicenseNumber;
			parameters[8].Value = model.PriceType;
			parameters[9].Value = model.UnitPrice;
			parameters[10].Value = model.TotalPrice;
			parameters[11].Value = model.BuyType;
			parameters[12].Value = model.Payments;
			parameters[13].Value = model.PayMoney;
			parameters[14].Value = model.Remark;
			parameters[15].Value = model.SignTime;
			parameters[16].Value = model.SignAddress;
			object obj = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
			if (obj == null)
			{
				return 1;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}

		public  SZSLSystem.Model.SubscriptionAgreement GetModel(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select top 1 [OID],[BuyerOID],[RoomOID],[RoomType],[ApprovalAuthority],[LicenseNumber],[Proprietorship],[GroundLicenseNumber],[PriceType],[UnitPrice],[TotalPrice],[BuyType],[Payments],[PayMoney],[Remark],[SignTime],[SignAddress] from SubscriptionAgreement");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			SZSLSystem.Model.SubscriptionAgreement model=new SZSLSystem.Model.SubscriptionAgreement();
			DataSet ds=DbHelperOleDb.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				DataRow row = ds.Tables[0].Rows[0];
						model.OID=row["OID"].ToString();
						model.BuyerOID=row["BuyerOID"].ToString();
						model.RoomOID=row["RoomOID"].ToString();
						model.RoomType=row["RoomType"].ToString();
						model.ApprovalAuthority=row["ApprovalAuthority"].ToString();
						model.LicenseNumber=row["LicenseNumber"].ToString();
						model.Proprietorship=row["Proprietorship"].ToString();
						model.GroundLicenseNumber=row["GroundLicenseNumber"].ToString();
						model.PriceType=row["PriceType"].ToString();
						model.UnitPrice=row["UnitPrice"].ToString();
						model.TotalPrice=row["TotalPrice"].ToString();
						model.BuyType=row["BuyType"].ToString();
						model.Payments=row["Payments"].ToString();
						model.PayMoney=row["PayMoney"].ToString();
						model.Remark=row["Remark"].ToString();
					if (row["SignTime"].ToString()!="")
					{
						model.SignTime=DateTime.Parse(row["SignTime"].ToString());
					}
						model.SignAddress=row["SignAddress"].ToString();

				return model;
			}
			else
			{
				return null;
			}
		}

		public int Update(SZSLSystem.Model.SubscriptionAgreement model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("update SubscriptionAgreement set ");
			strSql.Append("[OID]=@OID,");
			strSql.Append("[BuyerOID]=@BuyerOID,");
			strSql.Append("[RoomOID]=@RoomOID,");
			strSql.Append("[RoomType]=@RoomType,");
			strSql.Append("[ApprovalAuthority]=@ApprovalAuthority,");
			strSql.Append("[LicenseNumber]=@LicenseNumber,");
			strSql.Append("[Proprietorship]=@Proprietorship,");
			strSql.Append("[GroundLicenseNumber]=@GroundLicenseNumber,");
			strSql.Append("[PriceType]=@PriceType,");
			strSql.Append("[UnitPrice]=@UnitPrice,");
			strSql.Append("[TotalPrice]=@TotalPrice,");
			strSql.Append("[BuyType]=@BuyType,");
			strSql.Append("[Payments]=@Payments,");
			strSql.Append("[PayMoney]=@PayMoney,");
			strSql.Append("[Remark]=@Remark,");
			strSql.Append("[SignTime]=@SignTime,");
			strSql.Append("[SignAddress]=@SignAddress");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50),
					new SqlParameter ("@BuyerOID",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomOID",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomType",SqlDbType.VarChar,50),
					new SqlParameter ("@ApprovalAuthority",SqlDbType.VarChar,50),
					new SqlParameter ("@LicenseNumber",SqlDbType.VarChar,50),
					new SqlParameter ("@Proprietorship",SqlDbType.VarChar,50),
					new SqlParameter ("@GroundLicenseNumber",SqlDbType.VarChar,50),
					new SqlParameter ("@PriceType",SqlDbType.VarChar,50),
					new SqlParameter ("@UnitPrice",SqlDbType.VarChar,50),
					new SqlParameter ("@TotalPrice",SqlDbType.VarChar,50),
					new SqlParameter ("@BuyType",SqlDbType.VarChar,50),
					new SqlParameter ("@Payments",SqlDbType.VarChar,50),
					new SqlParameter ("@PayMoney",SqlDbType.VarChar,50),
					new SqlParameter ("@Remark",SqlDbType.VarChar,1000),
					new SqlParameter ("@SignTime",SqlDbType.DateTime),
					new SqlParameter ("@SignAddress",SqlDbType.VarChar,50)};
			parameters[0].Value = model.OID;
			parameters[1].Value = model.BuyerOID;
			parameters[2].Value = model.RoomOID;
			parameters[3].Value = model.RoomType;
			parameters[4].Value = model.ApprovalAuthority;
			parameters[5].Value = model.LicenseNumber;
			parameters[6].Value = model.Proprietorship;
			parameters[7].Value = model.GroundLicenseNumber;
			parameters[8].Value = model.PriceType;
			parameters[9].Value = model.UnitPrice;
			parameters[10].Value = model.TotalPrice;
			parameters[11].Value = model.BuyType;
			parameters[12].Value = model.Payments;
			parameters[13].Value = model.PayMoney;
			parameters[14].Value = model.Remark;
			parameters[15].Value = model.SignTime;
			parameters[16].Value = model.SignAddress;

			return DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
		}

		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select [OID],[BuyerOID],[RoomOID],[RoomType],[ApprovalAuthority],[LicenseNumber],[Proprietorship],[GroundLicenseNumber],[PriceType],[UnitPrice],[TotalPrice],[BuyType],[Payments],[PayMoney],[Remark],[SignTime],[SignAddress]");
			strSql.Append(" FROM SubscriptionAgreement ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public DataSet GetList(int top,string strWhere,string fieldOrder)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select");
			if(top>0)
			{
				strSql.Append(" top "+top.ToString());
			}
			strSql.Append(" [OID],[BuyerOID],[RoomOID],[RoomType],[ApprovalAuthority],[LicenseNumber],[Proprietorship],[GroundLicenseNumber],[PriceType],[UnitPrice],[TotalPrice],[BuyType],[Payments],[PayMoney],[Remark],[SignTime],[SignAddress]");
			strSql.Append(" FROM SubscriptionAgreement ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
				strSql.Append(" order by "+fieldOrder);
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public DataSet GetList(string showFields,string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
				strSql.Append(" select "+showFields);
			strSql.Append(" FROM SubscriptionAgreement ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public object GetSingle(string fieldName,string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
				strSql.Append(" select "+fieldName);
			strSql.Append(" FROM SubscriptionAgreement ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.GetSingle(strSql.ToString());
		}

	}
}